#! /usr/bin/env python
#coding=utf-8

"""
    sqlalchemy的测试1
"""

from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine("sqlite:///tutorial.db", echo = True)
metadata = MetaData()

users_table=Table('user',metadata,
    Column('user_id',Integer,primary_key=True),
    Column('user_name',String(40)),
    Column('password',String(10))
)

email_addresses_table=Table('email_addresses',metadata,
    Column('address_id',Integer,primary_key=True),
    Column('email_address',String(100),nullable=False),
    Column('user_id',Integer,ForeignKey('user.user_id'))
)


metadata.drop_all(engine)
metadata.create_all(engine)

ins = users_table.insert(values={'user_name':'jack', 'password':'Jack Jones'})
conn = engine.connect()
result = conn.execute(ins)

conn.execute(users_table.insert(), [
    {'user_name': 'Tom',},
    {'user_name': 'Fred',},
    {'user_name': 'Harry',},
])

conn.execute(email_addresses_table.insert(), [
    {'email_address':'tom@tom.com','user_id':2},
    {'email_address':'mary@mary.com','user_id':1},]
)


s = select([users_table, email_addresses_table], users_table.c.user_name == 'Tom', users_table.join(email_addresses_table))
result = conn.execute(s)
#result = conn.execute(users_table.join(email_addresses_table).select())
row = result.fetchone()
print row





